S09-04 MySQL-索引与事务

[TOC]

索引概述

索引定义

~~索引（Index）~~ 是 MySQL 存储引擎层用于快速定位数据行的一种预先排序的数据结构。

物理本质：在物理磁盘上，数据行以数据页（Page）为单位存储。索引作为独立维护的一组排序元数据，保存了“索引键值”与“物理数据行/主键”的映射寻址关系。
通俗比喻：索引类似于书籍开篇的目录，通过目录章节快速定位到具体页码，无需逐页翻看整本书。
核心价值：将无序数据的线性扫描（时间复杂度 $O(N)$ ）转化为有序结构的二分或树状查找（时间复杂度降至 $O(\log N)$ ），成倍降低磁盘 I/O 读取次数。

运行机制

数据库在执行数据检索时，是否命中索引对应两种截然不同的执行链路：

全表扫描模式（无索引）：
存储引擎需要从磁盘依次读取该表的每一个数据页加载至内存，逐行比对字段值是否满足查询条件。扫描行数等于全表总行数，耗时与 I/O 消耗随数据量线性增长。
索引寻址模式（有索引）：
存储引擎加载对应索引树的根节点，根据键值二分对比逐层跳转至目标叶子节点，精准定位包含目标记录的少量数据页，通常仅需 2 到 4 次磁盘 I/O 即可完成检索。

优势与代价

引入索引能够在提升查询效率的同时带来一定的系统维护开销，需要在设计阶段进行权衡。

~~核心优势~~

降低检索耗时：大幅缩减磁盘 I/O 次数，快速过滤非目标数据行。
加速分组与排序：索引本身按照特定顺序排列，ORDER BY 和 GROUP BY 可直接复用索引顺序，避免昂贵的临时表与外部文件排序（Filesort）。
保障数据唯一性：通过主键索引和唯一索引，在数据库层面强制实现字段值的唯一性约束。
优化表连接效率：在多表 JOIN 场景下，连接字段建立索引可将嵌套循环连接（Nested Loop Join）由全表扫描优化为高效的索引点查。

~~附带代价~~

磁盘空间占用：每个索引都需要构建独立的索引树，数据量较大且索引繁多时，索引文件体积可能接近甚至超过数据文件本体。
写入性能损耗：执行 INSERT、UPDATE、DELETE 等写操作时，存储引擎除了修改数据行，还必须实时维护并重新平衡索引树（如发生页分裂、页合并等操作）。

适用场景

结合业务访问模式与数据特征，索引的合理配置原则如下：

~~推荐建立场景~~

高频过滤字段：频繁出现在 WHERE 条件中的字段。
高基数列：字段取值区分度高（如用户唯一编号、手机号、订单流水号）。
关联与排序列：频繁作为 JOIN 条件关联的键，或频繁用于 ORDER BY、GROUP BY 的字段。

~~规避建立场景~~

低基数列：字段取值种类极少（如性别、流程状态、是否删除）。此类字段过滤后的结果集占比较高，优化器通常直接判定全表扫描更优。
微型数据表：全表仅有几十或数百行记录的小表，全表扫描耗时极短，建立索引反而徒增维护成本。
频繁变更字段：高频更新的列会导致索引树持续重构，显著拉低写吞吐量。
未截断长文本：超长字符串列（如 TEXT、BLOB）若不指定前缀长度，直接建索引会严重消耗数据页空间并降低单页索引项容纳量。

设计原则

~~索引设计的本质~~是用存储空间和写入性能换取查询效率。合理的设计能够在保障高频查询快速响应的同时，将写入损耗与存储冗余降至最低。

~~高基数优先~~

索引列的选择应以区分度（Cardinality）为核心指标。

区分度计算：区分度公式为 $\frac{\text{COUNT(DISTINCT col)}}{\text{COUNT(*)}}$ ，比值越接近 1，说明重复值越少，过滤效果越显著。
规避低基数列：对于性别、审核状态、逻辑删除标记等取值极少且分布集中的字段，建立单列索引收益极低。当查询命中行数超过全表的 20%~30% 时，优化器通常直接放弃索引转为全表扫描。
sql
```
-- 计算目标列的区分度以评估是否适合构建索引
SELECT
  COUNT(DISTINCT email) / COUNT(*) AS `selectivity`
  FROM user_account;
```
1
2
3
4

~~联合替代单列~~

当业务存在多条件组合过滤时，优先使用多列构成的联合索引替代多个单列索引。

最左前缀排列：根据最左前缀匹配原则，将区分度最高、使用频次最多的列置于联合索引的最左侧。

范围查询后置：在联合索引中，出现范围查询（如 >、<、BETWEEN）的字段会中断后续字段的索引走查，因此应将范围查询字段排在等值查询字段之后。

sql

-- 将高频等值字段放在左侧，范围查询字段排在后侧
CREATE INDEX idx_dept_status_salary
  ON employee (department_id, status, salary);

sql

-- 前两列命中等值匹配，最后一列命中范围匹配
EXPLAIN SELECT
  id, name
  FROM employee
  WHERE department_id = 10
  AND status = 1
  AND salary > 8000;

~~前缀截断~~

针对较长的字符串字段（如 VARCHAR(128) 或 VARCHAR(255)），直接全字段建立索引会导致数据页容纳项锐减并增加 B+ 树层高。

截断策略：截取字符串的前几个字符构建前缀索引，在维持较高区分度的前提下大幅缩减索引体积。

局限规避：前缀索引无法用于 ORDER BY 排序，也无法利用覆盖索引直接返回完整字符串。

sql

-- 评估不同前缀长度下的区分度表现
SELECT
  COUNT(DISTINCT LEFT(address, 10)) / COUNT(*) AS `sel_10`,
  COUNT(DISTINCT LEFT(address, 15)) / COUNT(*) AS `sel_15`
  FROM user_address;

-- 根据评估结果建立前缀索引
CREATE INDEX idx_address_prefix
  ON user_address (address(15));

~~覆盖优化~~

尽可能让联合索引包含查询所需的全部字段，构造覆盖索引（Covering Index）。

消除回表：当 SELECT 后的字段全部存在于二级索引树中时，存储引擎直接返回叶子节点的数据，避免了带着主键值二次访问聚簇索引的磁盘 I/O 开销。

列数权衡：不宜为了覆盖所有查询而盲目在联合索引中塞入过多宽列，宽索引会侵占单页存储容量并降低缓存命中率。

sql

-- 建立覆盖查询所需字段的复合索引
CREATE INDEX idx_user_email_created
  ON user_account (user_name, email, create_time);

sql

-- 执行计划 Extra 显示 Using index，完全无需回表
EXPLAIN SELECT
  email, create_time
  FROM user_account
  WHERE user_name = 'alex';

~~数量控制~~

索引并非越多越好，单张数据表的索引总量需严格控制。

单表上限建议：单表索引总数通常建议控制在 5 个以内。
写性能损耗：每一次 INSERT、UPDATE、DELETE 都需要同步维护所有关联的 B+ 树。索引过多会导致写吞吐量急剧下降，并增加页分裂、页合并与行锁竞争的概率。
避免频繁变更列：尽量不要在数据更新极其频繁的字段上建立索引，以防索引树持续重构引发性能抖动。

~~递增主键~~

InnoDB 是索引组织表，数据行的物理存储顺序与主键严格一致。

顺写优势：采用单调递增的主键（如自增 BIGINT 或雪花算法 ID），新数据始终追加在当前物理数据页的末尾，页面写满后自然开辟新页，数据页填充率高达 15/16。

乱序隐患：若使用无序字符串（如 UUID）作为主键，新记录需要随机插入到已满的历史数据页中，必然触发频繁的页分裂并产生大量磁盘碎片。

sql

-- 采用单调自增的主键设计保证顺序写入
CREATE TABLE `trade_order` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `order_no` VARCHAR(64) NOT NULL,
  `amount` DECIMAL(12, 2) NOT NULL,
  PRIMARY KEY (`id`)
);

常用语法

MySQL 提供了丰富的 DDL 与 DML 语法用于索引的创建、修改、查看、删除及查询干预。

建表创建

在创建数据表（CREATE TABLE）时直接定义索引是最常见的做法，支持主键、唯一、普通及全文等各类索引的声明。

PRIMARY KEY：声明主键索引，强制唯一且非空。
UNIQUE INDEX / KEY：声明唯一索引，保障字段或字段组合的唯一性。
INDEX / KEY：声明普通的单列索引或联合索引。

FULLTEXT INDEX：声明全文索引，用于文本内容的倒排检索。

sql

-- 建表同时声明主键索引、唯一索引与联合索引
CREATE TABLE `customer_profile` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `user_code` VARCHAR(32) NOT NULL,
  `mobile` VARCHAR(20) NOT NULL,
  `city` VARCHAR(50) NOT NULL,
  `age` INT NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `uk_user_code` (`user_code`),
  INDEX `idx_city_age` (`city`, `age`)
);

表后追加

针对已经存在的业务表，可以通过 ALTER TABLE 或 CREATE INDEX 语法追加新索引。

ALTER TABLE 方式：语法通用性强，支持追加主键、唯一键、普通索引和全文索引。
CREATE INDEX 方式：专门用于创建普通索引、唯一索引或全文索引（不支持直接创建主键）。

前缀索引语法：在长文本字段（如 VARCHAR）后加上 (length)，仅对字段前 N 个字符建索引。

sql

-- 通用写法：使用 ALTER TABLE 语法追加各类索引
ALTER TABLE `customer_profile`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE `uk_mobile` (`mobile`),
  ADD INDEX `idx_city` (`city`);

-- 前缀索引：为长字符串列创建指定长度的前缀索引
ALTER TABLE `customer_profile`
  ADD INDEX `idx_city_prefix` (`city`(10));

-- 全文索引：创建全文索引并指定 ngram 分词解析器
ALTER TABLE `customer_profile`
  ADD FULLTEXT INDEX `ft_city` (`city`) WITH PARSER ngram;

-- 使用 CREATE INDEX 语法创建联合索引
CREATE INDEX `idx_city_age_code`
  ON `customer_profile` (`city`, `age`, `user_code`);

索引查看

通过 SHOW INDEX 或查询元数据字典表，可以获取指定表上已存在的索引拓扑结构。

基本语法：SHOW INDEX FROM table_name; 或 SHOW KEYS FROM table_name;。
关键返回字段含义：
- Table：索引所属的表名。
- Non_unique：是否为非唯一索引（0 代表唯一索引/主键，1 代表允许重复的普通索引）。
- Key_name：索引名称（主键固定显示为 PRIMARY）。
- Seq_in_index：该列在联合索引中的顺序序号（从 1 开始，对最左前缀分析至关重要）。
- Column_name：索引所对应的字段名。
- Cardinality：基数估计值，表示索引中唯一值的预估数量，值越大通常区分度越高。
- Index_type：底层索引存储结构（InnoDB 默认为 BTREE，全文索引为 FULLTEXT）。
sql
```
-- 查询指定数据表的所有索引详情
SHOW INDEX FROM `customer_profile`;

-- 从系统视图中检索特定表的索引元数据
SELECT
  TABLE_NAME, INDEX_NAME, COLUMN_NAME, SEQ_IN_INDEX, NON_UNIQUE
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'customer_profile';
```
1
2
3
4
5
6
7
8
9

索引删除

当索引冗余、长期未被使用或需要重建时，可以通过 DROP INDEX 或 ALTER TABLE 语句将其清理。

ALTER TABLE 方式：支持删除常规索引以及专门的主键索引。

DROP INDEX 方式：直接针对表名和索引名进行删除。

sql

-- 使用 ALTER TABLE 语句删除普通索引或唯一索引
ALTER TABLE `customer_profile`
  DROP INDEX `uk_mobile`;

-- 使用 ALTER TABLE 移除表上的主键约束
ALTER TABLE `customer_profile`
  DROP PRIMARY KEY;

-- 使用 DROP INDEX 语句删除非主键索引
DROP INDEX `idx_city`
  ON `customer_profile`;

索引提示

在复杂的业务 SQL 查询中，如果 MySQL 查询优化器（Optimizer）选择了非预期的低效索引，可以通过~~索引提示（Index Hints）~~ 在 DML 语句中人工引导或强制优化器选择特定索引。

USE INDEX：建议优化器参考使用指定索引，优化器仍会评估成本并保留放弃的权利。
IGNORE INDEX：指示优化器在生成执行计划时忽略某些特定索引，强制不走该索引。

FORCE INDEX：强制优化器使用指定索引，大幅提升该索引的权重（即使优化器初评全表扫描代价可能更低）。

sql

-- 建议优化器优先考虑使用 idx_city_age 索引
SELECT
  `id`, `user_code`, `city`
  FROM `customer_profile` USE INDEX (`idx_city_age`)
  WHERE `city` = 'Shanghai'
    AND `age` >= 20;

-- 强制优化器必须走 uk_user_code 索引进行数据定位
SELECT
  `id`, `user_code`, `mobile`
  FROM `customer_profile` FORCE INDEX (`uk_user_code`)
  WHERE `user_code` = 'U9527';

-- 显式忽略 idx_city 索引，避免不必要的回表开销
SELECT
  `id`, `city`, `age`
  FROM `customer_profile` IGNORE INDEX (`idx_city`)
  WHERE `city` = 'Beijing'
  ORDER BY `id` DESC;

数据结构

MySQL 支持基于不同底层数据结构构建的索引：

B+Tree

结构演进

在数据库发展历程中，索引结构的演进经历了几代典型数据结构的权衡与淘汰：

二叉搜索树（BST）：在极端有序插入时会退化为单向链表，检索时间复杂度退化至 $O(N)$ 。
平衡二叉树（AVL / 红黑树）：虽然保证了平衡性，但每个节点只有 2 个子节点（出度太小）。存储千万级数据时，树高可达数十层。由于数据库数据存储在磁盘上，每一次向下寻址都对应一次随机磁盘 I/O，过深的树高会导致严重的性能瓶颈。
多路平衡查找树（B-Tree）：增大了节点分叉数（多路），显著降低了树高。但其每个节点（包含非叶子节点）都同时存储索引键与完整的行记录数据。由于单个磁盘页容量固定，数据行会挤占大量空间，导致单个节点能存储的索引键极少，树的高度依然不够扁平；且执行区间查询时需要跨层进行中序遍历，产生大量随机 I/O。
B+ 树（B+ Tree）：针对磁盘存储与范围查询专门优化的多路平衡树，克服了 B 树在数据库场景下的全部缺陷，成为 MySQL InnoDB 存储引擎的默认索引结构。

核心特征

B+ 树通过特定的结构设计，实现了高度极低、扫描高效的物理特征：

非叶子节点轻量化：非叶子节点仅存放索引键值（Key）与指向下一层子节点的页面指针（Pointer），不存放具体的行记录数据。这使得单个非叶子节点可以容纳上千个路由项，从而将整棵树的高度大幅压缩。
叶子节点全量存储：所有的实际数据（聚簇索引的整行记录，或二级索引的主键值）全部存储在底层的叶子节点中，任何查询都必须下潜至叶子节点才能取得数据，查询性能非常稳定。
叶子节点双向链表相连：所有叶子节点处于同一物理层级，且节点之间通过双向链表相互链接。当执行范围查询（如 BETWEEN、>、<）时，只需在树中定位起点，之后顺着链表顺序遍历即可，完全不需要在多层节点间来回递归跳跃。

页级存储

在 InnoDB 存储引擎中，B+ 树上的每一个节点对应物理磁盘上的一个数据页（Data Page），默认大小固定为 16 KB。

一个数据页内部由七个功能区域构成：

组成区域	空间大小	功能说明
File Header	38 字节	记录当前页的校验和、页号、上一页/下一页指针（构成双向链表）以及页类型。
Page Header	56 字节	记录页面的状态信息，如记录数量、空闲空间起始偏移量等。
Infimum + Supremum	26 字节	页面内的两个虚拟边界记录，分别代表该页内的最小记录与最大记录。
User Records	动态大小	实际存放的数据行，各行记录之间通过单向链表按照主键顺序组织。
Free Space	动态大小	尚未分配的剩余空间，供新写入的数据行使用。
Page Directory	动态大小	页目录。将 User Records 分组后，抽取每组最大记录的相对位置作为槽（Slot），支持在单个页内使用二分查找快速定位记录。
File Trailer	8 字节	页面尾部校验码，用于检测磁盘写入时是否发生断电等不完整写故障。

sql

-- 查看当前数据库实例配置的底层数据页大小
SHOW VARIABLES LIKE 'innodb_page_size'; -- 16K

检索链路

基于 B+ 树的数据查找分为“跨页路由定位”与“页内精确检索”两个阶段。

~~单行等值查询链路~~

根节点定位：InnoDB 在数据字典中直接获取索引根节点的页号（根页常驻内存）。
非叶子节点二分路由：读取节点页，对页内存储的有序索引键执行二分查找，锁定目标子节点所在的页号指针。
逐层下潜到叶子页：沿着指针继续向下层读取，直到命中承载实际记录的目标叶子节点页。
页目录二分锁定分组：进入叶子页后，利用该页的 Page Directory（页目录） 对槽位（Slot）进行二分查找，定位目标记录所在的分组（每个槽覆盖 4 到 8 条记录）。
单向链表遍历命中：从分组的起始记录沿着单链表线性比对 1 至 8 次，精确获取最终数据行。

~~范围查询链路~~

通过上述等值查询逻辑，在 B+ 树中快速定位范围区间的起始记录所在叶子页。
无需重新回溯树的上层节点，直接沿着叶子节点之间的双向链表向后（或向前）连续顺序读取数据页。
当读取到的键值超出查询范围边界时，立即终止扫描。

分裂与合并

由于 B+ 树节点的物理载体是 16 KB 的固定页，在频繁写操作下会触发结构重构。

~~页分裂~~

触发时机：向已写满的叶子页插入新记录，且没有足够的空闲空间容纳该行。
执行过程：
1. 存储引擎向表空间申请一个新的 16 KB 空白页。
2. 将原数据页中约 50% 的记录移动到新页中。
3. 修改原有双向链表的指针关系，将新页插入链表。
4. 向上一层非叶子节点插入新页的最小键值与页号指针。
工程影响：若主键使用非单调递增的随机值（如 UUID），会导致数据在任意中间页频繁触发随机分裂，产生大量的磁盘碎片与 I/O 停顿；而使用自增主键时，数据始终在最新页的尾部追加，页面写满后仅需直接开辟新页，不会造成中间节点分裂。

~~页合并~~

触发时机：删除记录或更新使得某页的数据利用率低于合并阈值（默认配置为 MERGE_THRESHOLD = 50%）。
执行过程：
1. 存储引擎检查其相邻页是否具备容纳剩余记录的能力。
2. 将该页的记录整体搬移合并至相邻页中。
3. 释放被合并页的空间，并从上一层非叶子节点中剔除对应的路由索引项。
sql
```
-- 建表时可针对特定索引定制页合并阈值
CREATE TABLE `order_record` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `order_no` VARCHAR(64) NOT NULL,
  `user_id` BIGINT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_user` (`user_id`) COMMENT 'MERGE_THRESHOLD=45'
);
```
1
2
3
4
5
6
7
8
说明：InnoDB 引擎在解析 COMMENT 字符串时会检查是否包含特定的"魔术关键词"。如果匹配到 MERGE_THRESHOLD=N，就会提取该值作为该索引的页合并阈值，而不会把它当作普通注释处理。

容量计算

B+ 树之所以具备极高的性能，核心在于其惊人的扇出比（Fan-out）。通过简单数学计算即可了解其层高与存储容量的关系。

扇出比（Fan-out）：在 InnoDB 的 B+ 树中，每个非叶子节点包含多个键值和对应的子指针，一个非叶子节点拥有的子指针数量就是扇出比。

~~计算模型假设~~

数据页固定大小： $16 \text{ KB} = 16384 \text{ 字节}$ 。
非叶子节点指针项：假设主键为 BIGINT（占用 8 字节），页面指针通常占用 6 字节，一个索引指针项共计 $8 + 6 = 14 \text{ 字节}$ 。
单个非叶子页容量：扣除页头页尾开销，单页约可存储 $16384 / 14 \approx 1170$ 个子页指针。
叶子节点数据行大小：假设单行数据（含业务字段与隐藏列）平均占用 $1 \text{ KB} = 1000 \text{ 字节}$ ，单页约可容纳 $16384 / 1000 \approx 16$ 行记录。

~~树高与容量对照~~

树层数（Height）	计算公式	理论最大可支撑记录数
1 层（根节点即叶子）	$16$	约 16 条
2 层（1 根 + 1170 叶）	$1170 \times 16$	约 1.87 万条
3 层（1 根 + 1170 支 + $1170^2$ 叶）	$1170 \times 1170 \times 16$	约 2190 万条
4 层（1 根 + 2 层分支 + 叶）	$1170 \times 1170 \times 1170 \times 16$	约 256 亿条

由此可见，在两千多万数据量级下，InnoDB 的 B+ 树仅需 3 层 即可承载全部数据。在实际运行中，根节点与第二层分支节点通常长期常驻在内存的 Buffer Pool 中，查询任意单行记录实际上仅需 1 次真实的磁盘 I/O 即可完成。

Hash【

基于哈希表实现的索引结构。

结构特点：
- 计算字段的哈希值直接映射到内存地址，单条记录等值查询（=、IN）的时间复杂度为 $O(1)$ 。
局限性：无法用于范围查询、排序以及最左前缀匹配。
应用场景：主要存在于 Memory 引擎；InnoDB 内部会自动监控热点数据页并创建自适应哈希索引（Adaptive Hash Index）以加速查询。

物理存储分类

在 InnoDB 引擎中，根据数据与索引是否集中存放，分为聚簇索引与二级索引。

聚簇索引

定义：将表中的主键值与实际行记录绑定存储在同一个 B+ 树中，叶子节点直接存放整行完整数据。
生成规则：
1. 优先将声明的 PRIMARY KEY 作为聚簇索引。
2. 若未显式声明主键，选择表中第一个定义为 NOT NULL 的唯一索引（UNIQUE）。
3. 若均不存在，InnoDB 会在内部隐式生成一个 6 字节的 row_id 作为聚簇索引。
数量限制：一张表有且仅有一个聚簇索引，物理存储顺序即按该索引排序。

二级索引

定义：除聚簇索引之外创建的所有索引（包括普通索引、唯一索引、联合索引）。
存储内容：二级索引的叶子节点仅存放索引列自身的值以及对应的主键 ID，不包含整行数据。
回表过程：若查询语句请求了非索引列字段，数据库必须先通过二级索引检索到主键 ID，再拿着主键 ID 到聚簇索引中检索整行数据，该动作称为回表（Table Lookup）。

逻辑功能分类

根据字段的业务逻辑约束与用途，索引分为以下四种：

主键索引

概念定义

~~主键索引（Primary Key Index）~~ 是关系型数据库中级别最高、约束最严的一种唯一性索引。

三大核心约束：
- 唯一性：表中任意两行记录的主键值绝不能重复。
- 非空性：主键列严禁包含 NULL 值，在定义时强制具备 NOT NULL 约束。
- 唯一实体：一张数据表有且仅能定义一个主键索引（允许由单个字段或多个字段组合构成）。
物理地位：在 MySQL 默认的 InnoDB 存储引擎中，主键索引直接等同于聚簇索引（Clustered Index），表中的全部数据行在物理磁盘上均依据主键顺序进行组织与存储。

存储机制

InnoDB 存储引擎是典型的“索引组织表”（Index-Organized Table），其数据存储与主键紧密绑定。

聚簇绑定：主键索引 B+ 树的叶子节点直接存储该行的完整业务数据（即包含所有列的行记录），而非仅存指针。通过主键定位即直接拿到整行数据。
主键生成降级规则：如果在建表时未显式指定主键，InnoDB 会按照以下优先级依次构建聚簇索引：
1. 优先使用用户显式声明的 PRIMARY KEY。
2. 若无显式主键，则选取表中第一个定义为 UNIQUE NOT NULL 的唯一索引列作为聚簇索引。
3. 若上述两者均不存在，InnoDB 会在底层自动生成一个名为 DB_ROW_ID 的 6 字节隐式单调递增行标识符作为聚簇索引。

主键选型

选择何种类型的值作为主键，直接决定了底层数据页的填充率、插入吞吐量以及存储碎片。

~~自增整数~~

优势：主键天然单调递增，新写入的数据行始终追加在当前物理数据页的末尾。当页面写满时，直接开辟新页（默认填充率可达 15/16），几乎不会触发中间节点的页分裂，写性能最高且磁盘碎片极少；占用体积小（INT 4 字节，BIGINT 8 字节）。
局限：在分布式分库分表或多主架构下难以直接保证全局唯一；连续递增的主键容易暴露业务订单量或注册用户数等敏感数据。

~~随机字符串~~

优势：全局唯一，可以在业务服务层本地无锁生成，适合跨系统数据汇总。
局限：UUID 字符串完全无序，新插入的数据必须随机插入到已存在的中间数据页中。若目标数据页已满，将强制触发页分裂并引发大量数据移动与磁盘碎片；同时占用空间大（VARCHAR(36)），导致所有二级索引体积成倍膨胀。

~~趋势递增分布式 ID~~

机制：通过“时间戳 + 机器标识 + 序列号”组成 64 位的 BIGINT 整数。
评估：既保证了在分布式集群下的全局唯一性，又兼具单调递增特性，写入时具备与自增主键接近的顺写性能，是高并发分布式业务场景下的主流选型。

索引联动

主键在整个表的索引体系中处于中枢地位，其设计优劣直接传导至所有二级索引（辅助索引）。

二级索引存储主键值：在 InnoDB 中，所有二级索引（如唯一索引、普通单列索引、联合索引）的叶子节点均不存物理行地址，而是直接存放对应行记录的主键值。
主键长度的放大效应：若主键体积庞大（例如使用 64 字节的复合字符串），表上建立的每一个二级索引都会冗余存储这份 64 字节的主键值，成倍增加磁盘占用与内存 Buffer Pool 的消耗。
检索效率差异：
- 主键点查：沿着聚簇索引树一次下潜即可直接获取整行数据，无需额外操作。
- 二级索引查找：先在二级索引树中检索到主键，再带着主键到聚簇索引树中进行回表（Bookmark Lookup）获取完整行。

语法操作

主键索引的基础 DDL 语句定义与查询计划验证：

sql

-- 创建表时定义自增 BIGINT 主键
CREATE TABLE `customer_order` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `order_sn` VARCHAR(32) NOT NULL,
  `user_id` BIGINT NOT NULL,
  `amount` DECIMAL(10, 2) NOT NULL,
  PRIMARY KEY (`id`)
);

sql

-- 为已有表追加主键约束
ALTER TABLE `customer_order`
  ADD PRIMARY KEY (`id`);

-- 移除表上的主键约束（若存在 AUTO_INCREMENT 需先修改列属性）
ALTER TABLE `customer_order`
  DROP PRIMARY KEY;

sql

-- 使用主键执行点查，执行计划显示访问类型达到最优的 const 级别
EXPLAIN SELECT
  `id`, `order_sn`, `amount`
  FROM `customer_order`
  WHERE `id` = 10001;

唯一索引

概念定义

唯一索引（Unique Index）是一种要求索引列中所有数据值必须唯一的索引结构。

双重属性：
- 性能属性：作为 B+ 树索引加速数据检索，等值查询在定位到第一条匹配记录后即可直接终止扫描。
- 约束属性：在数据库存储引擎层强制保障业务数据的唯一性，防止并发写入导致重复脏数据。
物理本质：在 InnoDB 存储引擎中，唯一索引属于二级索引（辅助索引），其叶子节点存储的是“索引字段键值 + 对应数据行的主键值”。

主键对比

主键索引与唯一索引在功能与底层机制上的核心差异：

对比维度	主键索引（Primary Key）	唯一索引（Unique Index）
数量限制	一张表有且仅能定义 1 个	一张表可以同时定义多个
空值约束	强制 `NOT NULL`，不允许出现 `NULL`	默认允许存在 `NULL` 值（除非显式指定 `NOT NULL`）
底层组织	构成聚簇索引，叶子节点直接存储整行数据	属于二级索引，叶子节点仅存储主键值（通常需回表）
写缓冲支持	不支持 Change Buffer	不支持 Change Buffer（普通二级索引支持）

空值特性

在 MySQL 中，NULL 代表“未定义”或“未知”，因此两个 NULL 进行等值比对时判定为“不相等”（即 NULL = NULL 结果为 UNKNOWN / FALSE）。

多 NULL 并存机制：如果未对唯一索引列添加 NOT NULL 约束，该列允许插入多行值为 NULL 的记录，存储引擎不会触发 Duplicate entry 冲突异常。
工程规避方案：在严谨的业务系统中，为避免 NULL 值破坏唯一性预期，通常将唯一索引字段显式声明为 NOT NULL DEFAULT '' 或 NOT NULL DEFAULT 0。

性能剖析

唯一索引与普通单列索引在查询与写入性能上存在显著的底层机制差异。

~~查询性能~~

普通索引：等值查找命中第一条记录后，需要沿着叶子节点链表继续读取下一条记录，直到遇到不相等的键值才停止。
唯一索引：因为键值具备唯一性，命中第一条记录后立刻终止扫描。
差异评估：InnoDB 数据以 16 KB 数据页为单位加载至内存 Buffer Pool 中，普通索引多比对一次指针只是微秒级的内存 CPU 计算，在绝大多数场景下两者读性能完全一致。

~~写入性能~~

普通索引：当目标数据页不在内存中时，InnoDB 会直接将写入/更新操作缓存在 Change Buffer（写缓冲） 中，稍后通过后台线程或后续读操作进行合并（Merge），无需立即产生随机磁盘 I/O。
唯一索引：在写入记录前，存储引擎必须首先校验该键值是否已存在。为了执行唯一性校验，必须强制将磁盘上的数据页同步加载进内存，完全无法利用 Change Buffer，从而在写密集型场景下产生显著更多的随机磁盘 I/O 开销。

语法操作

唯一索引的定义、追加与执行计划验证：

sql

-- 创建包含单列唯一索引的账号表
CREATE TABLE `account_user` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(100) NOT NULL,
  `user_name` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `uk_email` (`email`)
);

sql

-- 为已有表追加复合唯一索引（保障多字段联合唯一）
ALTER TABLE `account_user`
  ADD UNIQUE INDEX `uk_name_email` (`user_name`, `email`);

-- 移除指定的唯一索引
DROP INDEX `uk_email`
  ON `account_user`;

sql

-- 等值点查命中唯一索引，访问类型直接达到 const 级别
EXPLAIN SELECT
  `id`, `user_name`
  FROM `account_user`
  WHERE `email` = 'service@tech.com';

业务实践

在企业级架构落地中，唯一索引有两类高频的设计考量：

~~逻辑删除冲突处理~~

业务表采用软删除（如 is_deleted = 1）时，若对 (user_name) 建立唯一索引，同一用户被删除后将无法再次以相同名称注册。

时间戳联合方案：废除 is_deleted 状态位，改用 delete_time（未删除时固定为 0，删除时写入当前毫秒时间戳），构建联合唯一索引 (user_name, delete_time)。
物理归档方案：线上业务表只保留有效数据（强制删除时物理移除并转存至归档历史表），业务主表保持纯净的单列唯一索引。

~~防重复提交与幂等设计~~

在支付流水、订单防重等高并发接口中，利用业务要素生成唯一流水号（如 order_sn），在数据库层建立唯一索引。
并发请求到达时，依靠数据库底层的主键/唯一键冲突机制拦截重复写入，作为服务层分布式锁之外的最可靠兜底屏障。

普通索引

概念定义

普通索引（Normal Index），在 DDL 语句中通常使用 INDEX 或 KEY 关键字声明，是 MySQL 中最基础的一种索引形态。

纯粹检索加速：其唯一的设计目标是提升数据检索效率，在数据完整性上没有任何约束功能。
无唯一性约束：允许索引列中存在完全相同的重复值，同时也允许多个 NULL 值并存。
物理归属：在 InnoDB 存储引擎中，普通索引属于非聚簇索引（即二级索引 / 辅助索引），与主键聚簇索引共存。

存储机制

在 InnoDB 存储引擎中，普通索引以独立的 B+ 树形式组织并持久化在磁盘数据页中。

叶子节点内容：普通索引的叶子节点不存储整行记录，仅存放两项数据：索引列的键值以及该记录对应的主键值（Primary Key）。
节点排序规则：
1. 所有节点优先按照索引字段的值升序排列。
2. 当索引字段值相同时，按照对应的主键值升序排列。
空间轻量化：相比存储完整行记录的主键聚簇索引，普通索引的数据页体积较小，单页能容纳更多索引项，整棵索引树更加扁平。

回表机制

基于普通索引进行数据查询时，根据所请求的字段范围，会触发回表或直接命中覆盖索引。

~~回表查询流程~~

检索二级索引树：根据查询条件在普通索引 B+ 树上向下查找，定位到匹配的叶子节点，获取该行记录对应的主键值。
回表二次寻址：携带获取到的主键值，回到主键聚簇索引 B+ 树中重新进行二分查找。
读取完整行数据：在主键索引的叶子节点中取出包含所有业务字段的完整行记录。

~~覆盖索引优化~~

若 SQL 语句中 SELECT 请求的字段全部包含在当前普通索引树中（即仅查询索引列自身及主键列），存储引擎在完成二级索引查找后可直接返回结果。
此过程完全跳过了回表操作，避免了对主键索引树的二次磁盘 I/O 访问，执行计划中 Extra 字段将明确展示为 Using index。

写缓冲支持

相比唯一索引，普通索引具备显著的写性能优势，其核心依赖于 InnoDB 的 Change Buffer（写缓冲） 机制。

无需前置磁盘读取：普通索引不强制要求唯一性，写入新记录时不需要先读取磁盘页来校验值是否重复。
内存缓冲写入：若目标二级索引数据页当前未缓存在内存（Buffer Pool）中，InnoDB 会直接将 INSERT、UPDATE、DELETE 操作记录在 Change Buffer 中，立即完成写入并返回成功，不产生任何同步随机磁盘 I/O。
延迟合并（Merge）：当后续有查询操作需要访问该数据页，或者后台 Master Thread 定期轮询以及数据库正常关闭时，系统才会将 Change Buffer 中的修改操作合并写入到物理磁盘页中。

语法操作

普通索引的基础创建、修改、移除以及执行计划验证：

sql

-- 创建包含单列普通索引的员工信息表
CREATE TABLE `employee` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `department_id` INT NOT NULL,
  `salary` DECIMAL(10, 2) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_department_id` (`department_id`)
);

sql

-- 为已有表追加薪资字段的普通索引
ALTER TABLE `employee`
  ADD INDEX `idx_salary` (`salary`);

-- 移除指定的普通索引
DROP INDEX `idx_department_id`
  ON `employee`;

sql

-- 查询非索引字段，触发普通索引等值匹配并回表（访问类型为 ref）
EXPLAIN SELECT
  `id`, `name`, `salary`
  FROM `employee`
  WHERE `department_id` = 10;

sql

-- 仅查询索引列与主键，命中覆盖索引直接返回
EXPLAIN SELECT
  `id`, `department_id`
  FROM `employee`
  WHERE `department_id` = 10;

优化建议

在实际业务开发中，合理设计普通索引需重点考量以下工程原则：

关注区分度（基数 Cardinality）：优先在区分度高（COUNT(DISTINCT col) / COUNT(*) 接近 1）的字段上建立普通索引。如果列的基数极低（例如订单状态、用户性别），优化器评估后通常判定全表扫描代价更低，导致索引无法生效。
控制单表索引总量：每个普通索引都需要维护一棵独立的 B+ 树，过多的单列索引会显著拖慢批量数据插入与更新吞吐，单表索引数量通常建议控制在 5 个以内。
字符串前缀索引：针对较长的字符串字段（如 VARCHAR(128)），若需建立普通索引，可以通过 INDEX idx_title (title(20)) 指定前缀长度，在保障一定区分度的同时大幅减少索引占用的磁盘与内存空间。

全文索引

概念定义

全文索引（Full-Text Index）是专门用于在海量文本数据中快速检索关键字的索引结构。

解决痛点：传统的 B+ 树索引只能支持前缀匹配（如 LIKE 'tech%'），当执行包含前后通配符的模糊查询（如 LIKE '%tech%'）时，B+ 树索引将完全失效并触发全表扫描。全文索引从根本上解决了长文本关键词检索的性能瓶颈。
支持类型：仅支持在 CHAR、VARCHAR、TEXT 类型的文本列上构建。
引擎支持：MyISAM 引擎早期即原生支持；InnoDB 存储引擎自 MySQL 5.6 版本起全面内置支持。

倒排原理

全文索引的底层并没有采用常规的 B+ 树直接索引行数据，而是基于倒排索引（Inverted Index）机制构建。

正排与倒排对比：
- 正排索引（传统方式）：记录 ID $\to$ 文本内容（查找某条记录包含哪些词）。
- 倒排索引（全文检索）：分词词项（Word/Token） $\to$ 包含该词项的文档 ID 列表及文本偏移位置。
InnoDB 内部存储：
- 为支持快速查找，InnoDB 会在底层为包含全文索引的表隐式创建或维护一个 FTS_DOC_ID 列（8 字节单调递增整数）。
- 内存中通过 FTS Cache 暂存新增词项，后台刷新并持久化到 6 张物理辅助倒排表（Auxiliary Index Tables）中，按词项的哈希值分散存储。

检索模式

MySQL 全文检索提供了三种核心检索模式，通过 MATCH(...) AGAINST(...) 语法调用：

~~自然语言模式~~

默认模式：未指定模式时默认生效（IN NATURAL LANGUAGE MODE）。
工作机制：将检索字符串拆分为单词，计算每行记录与关键词之间的相关度得分（Relevance Score），默认按照得分从高到低排序返回。相关度根据词频（TF）与逆文档频率（IDF）等算法综合计算。

~~布尔模式~~

指定修饰符：使用 IN BOOLEAN MODE 声明。支持在搜索词中添加操作符进行精细逻辑控制：
- +：该单词必须出现（如 +MySQL）。
- -：该单词必须被排除（如 -Oracle）。
- *：通配符，仅支持词尾截断（如 datab* 匹配 database、datatable）。
- "：双引号包裹，强制精确短语匹配（如 "relational database"）。
- > / <：提升或降低该词对相关度的权重贡献。

~~查询扩展模式~~

隐式推导：使用 WITH QUERY EXPANSION 声明。
工作机制：分为两次搜索过程。第一次先进行常规自然语言检索；第二次将第一次命中的前几条记录中的高频关联词一并加入检索词库，执行二次扩展搜索，适用于“盲查”或探索性搜索。

中文分词

英文天然以空格和标点符号作为单词分隔符，而中文等东亚语言在词与词之间无空格，必须依赖分词插件。

ngram 分词器：MySQL 5.7 开始原生内置 ngram 全文分词插件，用于支持中文、日文、韩文的分词与索引。
切分机制（Sliding Window）：ngram 会按照固定长度将连续字符切片。例如对于文本“关系数据库”：
- 当分词长度为 2 时，切分为：关系、系数、数据、据库。
核心参数：
- ngram_token_size：定义分词切片长度，默认值为 2（通常无需调整）。
- ft_min_word_len / innodb_ft_min_token_size：控制索引收录的最小词长度。

语法操作

全文索引的创建、分词器指定与三种模式的查询语法示例：

sql

-- 创建支持中文分词全文索引的文章表
CREATE TABLE `tech_article` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `content` TEXT NOT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT INDEX `ft_title_content` (`title`, `content`) WITH PARSER ngram
);

sql

-- 使用布尔模式进行包含与排除过滤
SELECT
  `id`, `title`
  FROM `tech_article`
  WHERE MATCH(`title`, `content`) AGAINST('+数据库 -Oracle' IN BOOLEAN MODE);

sql

-- 获取相关度得分并按自然语言相关度降序排列
SELECT
  `id`, `title`,
  MATCH(`title`, `content`) AGAINST('分布式架构' IN NATURAL LANGUAGE MODE) AS `score`
  FROM `tech_article`
  WHERE MATCH(`title`, `content`) AGAINST('分布式架构' IN NATURAL LANGUAGE MODE)
  ORDER BY `score` DESC;

适用与边界

在技术架构选型中，MySQL 全文索引具备鲜明的优势与性能边界：

核心优势：直接依托现有 MySQL 数据库能力，无需引入、部署与维护额外的独立搜索引擎中间件，适合单表几十万至数百万级数据的轻量化文本检索需求。
性能边界：
- 写放大明显：高频更新文本字段会触发后台分词与倒排表的频繁重构，显著增加磁盘 I/O。
- 高级功能缺失：不具备拼音纠错、拼写联想、复杂语义分析及同义词近义词权重推导能力。
- 海量数据瓶颈：当文本数据达到数千万级或并发搜索 QPS 极高时，应考虑将数据同步至专业的搜索引擎（如 Elasticsearch / OpenSearch）。

字段维度分类

根据索引包含的字段列数进行划分：

单列索引

只基于一个数据列创建的索引。

联合索引

基于两个或两个以上字段组合创建的索引。

节点排序机制：在 B+ 树内部，先按第一个字段排序；当第一个字段取值相同时，再按第二个字段排序，依此类推。

sql

-- 创建包含联合索引的订单表
CREATE TABLE sys_order (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  status TINYINT NOT NULL,
  -- 建立 user_id 与 status 的联合索引
  KEY idx_user_status (user_id, status)
);

核心机制原则

合理使用索引需遵循以下三大底层机制：

最左前缀匹配原则

联合索引匹配时必须从最左列开始，不能跳过中间列。

假设存在联合索引 (a, b, c)：
查询条件包含 WHERE a=1 AND b=2 AND c=3：完全命中 (a, b, c)。
查询条件包含 WHERE a=1 AND b=2：命中 (a, b)。
查询条件包含 WHERE a=1 AND c=3：仅命中 (a)。
查询条件包含 WHERE b=2 AND c=3：完全无法命中索引。
范围中断：若遇到范围查询（>, <, BETWEEN），该字段后的其余字段将无法继续走索引树精确检索。

覆盖索引

若一条查询语句所需返回的所有字段都在当前使用的索引树中已存在，则无需进行回表操作。

sql

-- 触发覆盖索引：id、user_id、status 均可在 idx_user_status 树中直接获取
SELECT id, user_id, status
  FROM sys_order
  WHERE user_id = 1001;

索引下推

MySQL 5.6 引入的优化机制。

当使用联合索引检索时，存储引擎层在遍历索引时会优先判断索引中包含的其余条件，将不符合条件的记录直接过滤，随后再回表读取完整行数据，从而大幅降低回表 I/O 成本。

常见失效场景

编写 SQL 时需避免以下触发索引失效的情况：

对索引列进行函数或计算：WHERE SUBSTRING(name, 1, 3) = 'abc' 或 WHERE age + 1 = 18。
隐式类型转换：字符串列与数值类型直接比对，如 WHERE mobile = 13800000000（mobile 为 VARCHAR 时会触发内置转换函数从而失效）。
左侧模糊匹配：WHERE title LIKE '%mysql' 无法走索引树；而 WHERE title LIKE 'mysql%' 可以命中。
OR 条件未全部建立索引：WHERE a = 1 OR b = 2 中，只要 b 字段无索引，整个查询均转为全表扫描。
违背最左前缀原则：跳过联合索引前导字段直接使用后续字段进行过滤。

事务

基础概念

~~事务（Transaction）~~ 是数据库管理系统执行过程中的一个逻辑处理单元，由一条或多条 SQL 语句组成。事务内的所有操作作为一个整体向系统提交，要么全部执行成功，要么全部执行失败。

事务定义

在数据库中，事务主要用于维护数据的一致性与完整性。当多个用户并发访问同一数据，或者系统在执行数据更新过程中遭遇断电、崩溃等异常情况时，事务机制可以防止数据出现部分写入或状态错乱。

引擎支持

MySQL 的事务支持由存储引擎层实现，不同的存储引擎对事务的支持能力不同：

InnoDB：MySQL 默认的事务型存储引擎，完整支持 ACID 特性、行级锁与崩溃恢复。
NDB Cluster：分布式存储引擎，支持分布式事务。
MyISAM / Memory：非事务型引擎，每条语句执行后立即生效，无法通过回滚撤销操作。

ACID 特性

数据库事务必须满足 ~~ACID 四大特性~~：

~~1. 原子性（Atomicity）~~：

基本概念：原子性要求事务是不可分割的最小工作单位。事务中的全部操作要么全部提交成功，要么在发生错误时全部回滚至最初状态。
通俗理解：银行转账包含“A 账户扣款”与“B 账户收款”两个动作，不能出现 A 扣款成功而 B 未收到款项的中间割裂状态。
底层支撑：通过 Undo Log（回滚日志） 实现。当操作失败或执行回滚指令时，系统根据 Undo Log 记录的反向逻辑执行逆向补偿操作。

~~2. 一致性（Consistency）~~：

基本概念：一致性要求事务执行前后，数据库从一个合法的完整状态转换到另一个合法的完整状态。所有的完整性约束（如主键唯一性、外键关联、非空约束及业务逻辑规则）均未被破坏。
通俗理解：转账前后，无论转账操作成功还是失败，A 和 B 两个账户的资金总和在业务逻辑上必须保持恒定守恒。
底层支撑：一致性是事务追求的最终目标，由原子性、隔离性、持久性共同保障，并依赖数据库约束机制与应用层业务逻辑校验。

~~3. 隔离性（Isolation）~~：

基本概念：隔离性要求并发执行的多个事务之间相互独立，一个事务内部的操作及产生的数据中间状态对其他并发事务是不可见的。
通俗理解：多个用户同时对数据库进行读写操作时，各个会话仿佛在互相独立的空间中运行，彼此互不干扰。
底层支撑：通过 锁机制（Locking） 保证写写并发安全，通过 MVCC（多版本并发控制） 实现读写并发互不阻塞。

~~4. 持久性（Durability）~~：

基本概念：持久性要求事务一旦提交成功，其对数据库中数据的修改就是永久性的，后续即使发生系统宕机、断电等硬件故障，数据也不会丢失。
通俗理解：只要转账操作提示“成功”，即使服务器随后立刻断电重启，账户余额依然是更新后的数值。
底层支撑：通过 Redo Log（重做日志） 与 WAL（Write-Ahead Logging，预写日志） 机制实现。事务提交时优先顺序写入日志，重启时通过重放日志恢复未刷入磁盘表空间的数据。

事务状态

事务在执行生命周期中会经历以下五种状态的流转：

活动的（Active）：事务处于执行状态，正在顺序处理读写操作。
部分提交的（Partially Committed）：事务中的最后一条语句已经执行完毕，但修改的数据暂存在内存缓冲区，尚未完全持久化到磁盘。
失败的（Failed）：事务在执行过程中遇到错误（如语法错误、约束冲突、死锁），或被主动终止。
中止的（Aborted）：事务进入失败状态后，系统完成回滚并将数据恢复到事务开始前的状态。
提交的（Committed）：事务的所有修改已成功写入存储并持久化，生命周期正常结束。

事务生命周期

MySQL 默认开启自动提交（autocommit = 1），即每条单独的 DML 语句都会被视作一个独立事务并自动提交。若要组合多条语句，需使用显式事务控制。

sql

-- 开启显式事务并执行扣减与充值
START TRANSACTION;

UPDATE user_wallet
  SET balance = balance - 100.00
  WHERE user_id = 101;

-- 设置局部保存点
SAVEPOINT sp1;

UPDATE user_wallet
  SET balance = balance + 100.00
  WHERE user_id = 102;

-- 提交事务完成持久化
COMMIT;

START TRANSACTION / BEGIN：显式开启一个新事务。
COMMIT：提交事务，持久化所有变更并释放相关锁资源。
ROLLBACK：回滚当前事务的所有变更。
ROLLBACK TO [SAVEPOINT]：回滚至指定的保存点，保留保存点之前的操作。

基本操作

在 MySQL（InnoDB 引擎）中，事务的基本操作涵盖了自动提交控制、显式开启、提交、回滚、保存点以及系统监控。

一个完整的事务操作从开启事务（Begin Transaction）开始，经过一系列 DML 语句执行，最终根据执行结果走向提交（Commit Changes）固化数据，或在遇到错误时走向回滚（Rollback Changes）恢复现场。

提交控制

MySQL 默认处于自动提交状态（autocommit = ON）。这意味着每一条独立的 DML 语句（如 INSERT、UPDATE、DELETE）在执行完毕后都会被系统自动隐式提交，无法通过 ROLLBACK 撤销。

sql

-- 1. 查询当前会话与全局的自动提交状态
SELECT @@autocommit, @@global.autocommit;
-- 2. 关闭当前会话的自动提交（0 表示关闭，1 表示开启）
SET autocommit = 0;

自动提交开启（1）：单条写语句执行完立即持久化。
自动提交关闭（0）：所有写操作均停留在当前事务中，必须显式执行 COMMIT 才会写入磁盘，执行 ROLLBACK 则撤销当前全部变更。

开启事务

显式开启事务可以临时挂起当前的单语句自动提交机制，将后续的多条 SQL 语句归纳在同一个事务工作单元中。

sql

-- 1. 标准显式开启事务（读写模式）
START TRANSACTION;
-- 2. 开启只读事务（禁止当前事务执行写操作，优化只读查询性能）
START TRANSACTION READ ONLY;
-- 3. 开启一致性快照事务（立刻建立 MVCC ReadView，常用于数据备份）
START TRANSACTION WITH CONSISTENT SNAPSHOT;

BEGIN 或 BEGIN WORK：标准 START TRANSACTION 的简写形式。
WITH CONSISTENT SNAPSHOT：仅在 REPEATABLE READ 隔离级别下生效，在开启事务的瞬间直接生成一致性视图，而不是等到第一条 SELECT 时才生成。

提交事务

提交操作会将当前事务内发生的所有数据变更永久写入存储引擎（Redo Log 刷盘并更新内存脏页），同时释放事务所持有的行锁和表锁资源。

sql

-- 1. 开启事务
START TRANSACTION;
-- 2. 执行扣减库存操作
UPDATE product_stock
  SET stock = stock - 1
  WHERE product_id = 1001;
-- 3. 执行新增订单记录
INSERT INTO order_record (order_id, user_id, amount)
  VALUES (2026081901, 101, 99.00);
-- 4. 提交事务（持久化数据变更并释放持有的行锁）
COMMIT;

执行流程

客户端向服务器发送 COMMIT 指令。
存储引擎完成两阶段提交（Redo Log 状态置为 commit，Binlog 写入完成）。
释放事务占用的行锁资源。
当前事务生命周期终结。

回滚事务

当事务执行期间发生异常（如主键冲突、外键受限、余额不足或客户端断开连接）时，执行回滚操作可以撤销当前事务内的所有未提交变更，数据恢复至事务开启前的状态。

sql

-- 1. 开启事务
START TRANSACTION;
-- 2. 执行扣款操作
UPDATE account
  SET balance = balance - 500
  WHERE user_id = 1;
-- 3. 业务校验失败或捕获异常，执行全量回滚
ROLLBACK;

执行流程

客户端发送 ROLLBACK 指令（或连接异常断开触发服务端自动回滚）。
存储引擎读取当前事务的 Undo Log（回滚日志）。
按照逆序依次执行反向补偿操作（例如将修改的字段改回原值，删除刚刚插入的行）。
释放所有锁资源并结束事务。

保存点管理

保存点（Savepoint）用于在事务内部设置阶段性检查点，允许事务实现部分回滚，而无需将整个事务的操作全部撤销。

sql

-- 1. 开启事务
START TRANSACTION;
-- 2. 插入第一批积分明细
INSERT INTO points_log (user_id, points)
  VALUES (1, 100);
-- 3. 创建保存点 sp1
SAVEPOINT sp1;
-- 4. 插入第二批积分明细（后续发现参数有误）
INSERT INTO points_log (user_id, points)
  VALUES (2, 200);
-- 5. 回滚到保存点 sp1（仅撤销第二批明细，第一批仍然保留）
ROLLBACK TO SAVEPOINT sp1;
-- 6. 删除并释放保存点 sp1 占用的内存资源
RELEASE SAVEPOINT sp1;
-- 7. 提交事务（最终第一批数据成功入库）
COMMIT;

SAVEPOINT <name>：在事务内标记一个命名的检查点。
ROLLBACK TO [SAVEPOINT] <name>：将状态回滚到指定保存点，保存点之后的操作全部作废，但保存点之前的操作仍然有效。
RELEASE SAVEPOINT <name>：删除指定保存点，不再支持回滚到该点（不影响数据的提交或回滚）。

隐式提交

在 MySQL 中，如果在事务尚未显式调用 COMMIT 或 ROLLBACK 时执行了某些特定类型的 SQL 语句，MySQL 会强制自动提交当前正在运行的事务。

触发隐式提交的常见场景

DDL 语句：CREATE TABLE、ALTER TABLE、DROP TABLE、TRUNCATE TABLE。
权限与管理语句：GRANT、REVOKE、CREATE USER、SET PASSWORD。
事务控制语句嵌套：在一个未结束的事务中再次执行 START TRANSACTION 或 BEGIN。
锁表语句：LOCK TABLES、UNLOCK TABLES。
表维护语句：ANALYZE TABLE、CHECK TABLE、OPTIMIZE TABLE。

事务监控

对于生产环境中出现的锁等待、长事务阻塞等问题，可以通过系统视图查询实时事务状态并进行干预。

sql

-- 1. 查询当前实例中所有活跃（运行中或锁等待）的事务详情
SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query
  FROM information_schema.INNODB_TRX;
-- 2. 针对严重阻塞系统的长事务线程，手动终止连接（例如杀死线程 105）
KILL 105;

trx_state：事务当前状态（如 RUNNING 正在运行，LOCK WAIT 等待锁）。
trx_started：事务启动时间，可用于排查未及时提交的长事务。
trx_mysql_thread_id：对应的 MySQL 客户端连接线程 ID，可配合 KILL 命令终止会话。

隔离级别

在数据库并发操作中，多个事务同时访问和修改相同的数据，需要在数据一致性与并发处理性能之间做出权衡。SQL-92 标准定义了 4 种事务隔离级别，MySQL 的 InnoDB 存储引擎对其均提供了完整支持。

隔离背景

当多个事务并发执行时，如果不进行有效的隔离控制，会导致读写冲突与数据混乱。隔离级别用于定义一个事务对数据的修改在何时、以何种方式对其他并发事务可见。

隔离级别越高，数据一致性保障越强，但系统的并发吞吐量与性能消耗越明显。

并发异常

不同的隔离级别主要为了解决并发事务中的三类数据读取异常：

脏读（Dirty Read）：事务 A 读取到了事务 B 尚未提交的数据。若事务 B 随后回滚，事务 A 读取到的就是不合法的“脏数据”。
不可重复读（Non-repeatable Read）：事务 A 在同一事务内两次读取同一行记录，在两次读取之间，事务 B 修改或删除并提交了该记录，导致事务 A 两次读取的值不一致。
幻读（Phantom Read）：事务 A 按特定范围条件查询数据，事务 B 新增并提交了满足该条件的新记录，导致事务 A 再次以相同条件检索或执行范围更新时，多出了原本不存在的数据行。

隔离级别

4 种隔离级别的防护能力与默认配置对照如下：

隔离级别	脏读	不可重复读	幻读	典型默认系统
读未提交（Read Uncommitted）	允许	允许	允许	极少使用
读已提交（Read Committed）	解决	允许	允许	Oracle, PostgreSQL, SQL Server
可重复读（Repeatable Read）	解决	解决	解决（InnoDB 引擎）	MySQL InnoDB
串行化（Serializable）	解决	解决	解决	极少使用

读未提交

基本原理：读未提交（Read Uncommitted）是隔离级别中限制最弱的一档。事务中的修改即使尚未提交，对其他并发事务也是立即可见的。
底层行为：读取数据时不加共享锁，也不生成 MVCC 一致性快照，直接读取内存中的最新数据页。
并发缺陷：脏读、不可重复读、幻读均可能发生。

应用场景：数据准确性要求极低、仅做全量粗略统计且极度追求读取性能的特殊场景，生产业务中极少启用。

sql

-- 1. 设置会话隔离级别为读未提交
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
-- 2. 查询可能读取到其他事务未提交的脏数据
SELECT user_id, balance
  FROM account
  WHERE user_id = 1;

读已提交

基本原理：读已提交（Read Committed, 简称 RC）保证事务只能读取到其他事务已经提交的数据变更，彻底杜绝脏读。
底层行为：基于 MVCC（多版本并发控制）实现。事务中每一次执行 SELECT 查询时，都会重新生成一份最新的 ReadView 快照。
并发缺陷：解决了脏读；但因为每次查询都生成新快照，仍会出现不可重复读与幻读。

应用场景：Oracle、PostgreSQL 等数据库的默认级别。在高并发互联网写入业务中常被选用，因其只使用记录锁（无间隙锁），锁冲突少、死锁概率低。

sql

-- 1. 设置会话隔离级别为读已提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
-- 2. 仅能读取到已提交的数据，多次执行可能读到其他事务新提交的变更
SELECT user_id, balance
  FROM account
  WHERE user_id = 1;

可重复读

基本原理：可重复读（Repeatable Read, 简称 RR）是 MySQL InnoDB 引擎的默认隔离级别。它保证事务在运行期间多次读取同一记录时，其结果完全一致。
底层行为：

快照读（普通 SELECT）：基于 MVCC 实现，仅在事务执行第一次 SELECT 时生成一次 ReadView 快照，后续整个事务周期复用该视图，解决不可重复读。
当前读（SELECT ... FOR UPDATE 或更新操作）：结合 Next-Key Lock（记录锁与间隙锁组合） 锁定查询范围，防止其他事务插入新行，解决幻读问题。
- 并发缺陷：解决了脏读、不可重复读，并在 InnoDB 中基本消除了幻读。
  sql
```
-- 1. 设置会话隔离级别为可重复读
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
-- 2. 事务内多次执行该查询，结果始终保持一致
SELECT user_id, balance
  FROM account
  WHERE user_id = 1;
```
  1
  2
  3
  4
  5
  6
  7

串行化

基本原理：串行化（Serializable）是最高强度的隔离级别。它将所有并发事务强制转为按序串行执行。
底层行为：InnoDB 会将所有普通的读操作（SELECT）隐式转换为加读锁模式（SELECT ... LOCK IN SHARE MODE），读写完全互斥。
并发缺陷：彻底解决了脏读、不可重复读、幻读所有异常。

代价值：并发吞吐量极低，事务排队等待严重，极易产生锁等待超时或死锁，仅适用于对数据绝对敏感且并发极低的场景。

sql

-- 1. 设置会话隔离级别为串行化
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
-- 2. 普通查询会被隐式添加共享锁，阻塞其他写事务
SELECT user_id, balance
  FROM account
  WHERE user_id = 1;

配置管理

可以通过系统变量在全局或会话维度查询和调整隔离级别。

sql

-- 1. 查询当前会话与全局的隔离级别（MySQL 8.0+ 推荐变量名）
SELECT @@transaction_isolation, @@global.transaction_isolation;
-- 2. 调整当前会话的隔离级别（仅对当前客户端连接生效）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 3. 调整全局隔离级别（对之后建立的新连接生效，已有连接不变）
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

练习：

事务的课堂练习：
登录mysql控制客户端A，创建表dog (id, name)，开始一个事务，添加两条记录;
登录mysql控制客户端B，开始一个事务，设置为读未提交.
A客户端修改dog一条记录,不要提交。看看B客户端是否看到变化，说明什么问题?
登录mysql客户端C，开始一个事务，设置为读已提交，这时A客户修改一条记录，不要提交，看看C客户端是否看到变化,说明什么问题?

MVCC 机制

多版本并发控制（Multi-Version Concurrency Control, MVCC）是 InnoDB 实现高性能并发读取的核心技术。它通过维护数据的历史版本，实现“读不加锁，读写不冲突”。

隐藏字段与版本链

InnoDB 在每行聚簇索引记录后都会隐式添加三个系统字段：

DB_TRX_ID：最近一次插入或修改该记录的事务 ID（6 字节）。
DB_ROLL_PTR：回滚指针，指向写入 Undo Log 的上一版本历史记录（7 字节）。

DB_ROW_ID：隐式自增行 ID，在没有显式主键和唯一索引时生成（6 字节）。

[ 聚簇索引行记录 ]
+---------+---------------+----------------+-----------------+
| user_id | balance       | DB_TRX_ID (10) | DB_ROLL_PTR     |
+---------+---------------+----------------+--------│--------+
                          │
  ┌───────────────────────────────────────────────┘
  ▼
[ Undo Log 版本链 ]
+---------------+----------------+-----------------+
| balance: 90.0 | DB_TRX_ID (8)  | DB_ROLL_PTR     |
+---------------+----------------+--------│--------+
                      ▼
+---------------+----------------+-----------------+
| balance: 50.0 | DB_TRX_ID (5)  | NULL            |
+---------------+----------------+-----------------+

读视图 (ReadView) 结构

当事务执行快照读（普通 SELECT）时，系统会生成一个一致性读视图（ReadView），其核心结构包含：

m_ids：在生成 ReadView 的那一刻，系统中所有活跃且未提交的事务 ID 列表。
min_trx_id：m_ids 中的最小值。
max_trx_id：生成 ReadView 时，系统即将分配给下一个事务的 ID 值（非最大活跃 ID，而是最大分配 ID + 1）。
creator_trx_id：创建该 ReadView 的当前事务 ID。

可见性判断流程

事务根据 Undo Log 链逐版本比对，判定某版本记录（事务 ID 为 trx_id）是否对其可见：

          trx_id (版本事务ID)
              │
    ┌───────────────────┼───────────────────┐
    ▼                                       ▼
  trx_id < min_trx_id                     trx_id >= max_trx_id
 (该事务在快照前已提交)                     (该事务在快照后才开启)
   [ 判定：可见 ]                          [ 判定：不可见 ]
    │
    └───────────────┬───────────────────────┘
            ▼
      min_trx_id <= trx_id < max_trx_id
            │
       ┌──────────┴──────────┐
       ▼                     ▼
     trx_id 在 m_ids 中      trx_id 不在 m_ids 中
     (活跃中，尚未提交)        (已在快照前提交)
     [ 判定：不可见 ]         [ 判定：可见 ]

特殊规则：若 trx_id == creator_trx_id，说明是当前事务自身所作的修改，判定可见。
RC 与 RR 的本质差异：
RC 级别：事务中每执行一次 SELECT 都会重新生成一个最新的 ReadView。
RR 级别：事务中首次执行 SELECT 时生成 ReadView，后续整个事务生命周期内一直复用该视图。

锁机制

InnoDB 采用多粒度锁与不同的行锁算法来协调并发写操作及解决幻读问题。

锁分类与兼容性

读写锁（粒度维度）：
共享锁 (S Lock)：允许事务读取一行数据，阻止其他事务获取相同数据集的排他锁。
排他锁 (X Lock)：允许事务更新或删除数据，阻止其他事务获取任何共享锁或排他锁。
意向锁（表级辅助锁）：
意向共享锁 (IS) 与 意向排他锁 (IX)：由存储引擎在获取行级 S/X 锁之前自动于表级别添加，用于快速判断表内是否存在行锁，避免全表扫描校验。

行锁的三种算法

[ 索引记录分布：Record 10, Record 20, Record 30 ]

Record Lock:      [10]            [20]            [30]
Gap Lock:              (10, 20)        (20, 30)
Next-Key Lock:         (10, 20]        (20, 30]

记录锁 (Record Lock)：仅锁定索引中的单个特定条目。
间隙锁 (Gap Lock)：锁定索引记录之间的开区间，防止其他事务在此区间内插入新数据，专用于消除 RR 级别的幻读。
临键锁 (Next-Key Lock)：记录锁与间隙锁的组合，锁定一个左开右闭区间（如 (10, 20]）。InnoDB 在 RR 隔离级别下的默认行锁形态。

死锁与检测

当两个或多个事务互相持有对方需要的锁资源且均不释放时，便构成死锁。

死锁预防机制：通过参数 innodb_deadlock_detect = ON 开启死锁检测，InnoDB 在发现死锁环路后，会自动选择回滚 Undo 较小的事务（代价最低的事务）以解除死锁。
超时中断机制：通过参数 innodb_lock_wait_timeout（默认 50 秒）控制锁等待超时阈值。

日志保障

InnoDB 借助预写式日志（Write-Ahead Logging, WAL）机制确保数据修改在写入磁盘前先持久化日志，兼顾性能与安全性。

[ 事务写操作执行流程 ]
SQL 执行 -> 修改 Buffer Pool 数据页
      │
      ├─ 写入 Undo Log (记录反向回滚操作)
      │
      ├─ 写入 Redo Log Buffer (记录物理数据页修改)
      │
      ▼
[ 事务提交阶段：两阶段提交 (2PC) ]

1. Prepare 阶段: Redo Log 刷盘 (标记为 prepare 状态)

2. Commit  阶段: Binlog 刷盘

3. 完成提交    : Redo Log 标记为 commit 状态

重做日志 (Redo Log)

作用：保障事务的持久性与崩溃恢复能力 (Crash-Safe)。
写入机制：采用固定大小的物理空间环形写入（Circular Log File），包含 Checkpoint（检查点）与 Write Pos（写入位点）。
刷盘策略：由参数 innodb_flush_log_at_trx_commit 控制：
0：每秒将 Redo Log Buffer 写入操作系统缓存并刷盘（最快，宕机丢失 1 秒数据）。
1（默认/最安全）：每次事务提交都强制写入磁盘并调用 fsync。
2：每次事务提交写入操作系统缓存，由系统每秒自动刷盘一次。

回滚日志 (Undo Log)

作用：保障事务的原子性（执行失败时依靠逻辑反向操作回滚）以及支持 MVCC 多版本链。
清理机制：INSERT 操作产生的 Undo Log 在事务提交后可立即删除；UPDATE/DELETE 产生的 Undo Log 需保留供快照读使用，待没有更早的 ReadView 引用时由后台 Purge 线程统一清理。

两阶段提交 (Two-Phase Commit)

为了保证跨引擎层与服务层日志（InnoDB Redo Log 与 Server 层 Binlog）的数据一致性，MySQL 采用两阶段提交：

Prepare 阶段：InnoDB 将事务操作写入 Redo Log 并将状态标记为 prepare，执行刷盘。
Binlog 写入：Server 层将事务操作写入 Binlog 并持久化至磁盘。
Commit 阶段：InnoDB 在 Redo Log 中将状态更新为 commit，事务完成。
- 崩溃恢复逻辑：若在写入 Binlog 之前系统宕机，重启后检测到 Redo Log 处于 prepare 且 Binlog 中无对应 XID 记录，则执行回滚；若 Binlog 已写入完整记录，即使 Redo Log 未及更新为 commit，引擎也会前滚提交该事务。

生产实践

在生产环境中，事务设计直接影响系统的吞吐量、锁等待与存储容量。

长事务的危害与排查

长事务会长期占用连接资源与行锁，并阻止 Undo Log 空间回收，导致回滚表空间膨胀及全表扫描性能下降。

sql

-- 查询运行时间超过 5 秒的未提交事务
SELECT
  trx_id, trx_state, trx_started,
  NOW() - trx_started AS duration_seconds,
  trx_mysql_thread_id, trx_query
  FROM information_schema.innodb_trx
  WHERE NOW() - trx_started > 5;

-- 终止导致长时间阻塞的会话连接
KILL 12345;

事务开发与调优准则

控制事务粒度：避免将网络 I/O、远程 RPC 调用或耗时复杂的非数据库操作置于数据库事务中。
避免隐式提交：事务内严禁执行 DDL 语句（如 CREATE TABLE、ALTER TABLE 等），这类语句会强制触发当前事务的隐式提交。
有序访问资源：在并发批量更新时，各个业务事务应遵循全局一致的更新顺序（例如按记录主键升序加锁更新），从逻辑上规避循环依赖型死锁。
读写分离与快照选择：对于纯报表统计类只读大查询，尽量走只读从库，减少对主库 MVCC Undo 链及 Buffer Pool 的压力。

S09-04 MySQL-索引与事务 ​

索引概述 ​

索引定义 ​

运行机制 ​

优势与代价 ​

适用场景 ​

设计原则 ​

常用语法 ​

建表创建 ​

表后追加 ​

索引查看 ​

索引删除 ​

索引提示 ​

数据结构 ​

B+Tree ​

结构演进 ​

核心特征 ​

页级存储 ​

检索链路 ​

分裂与合并 ​

容量计算 ​

Hash【 ​

物理存储分类 ​

聚簇索引 ​

二级索引 ​

逻辑功能分类 ​

主键索引 ​

概念定义 ​

存储机制 ​

主键选型 ​

索引联动 ​

语法操作 ​

唯一索引 ​

概念定义 ​

主键对比 ​

空值特性 ​

性能剖析 ​

语法操作 ​

业务实践 ​

普通索引 ​

概念定义 ​

存储机制 ​

回表机制 ​

写缓冲支持 ​

语法操作 ​

优化建议 ​

全文索引 ​

概念定义 ​

倒排原理 ​

检索模式 ​

中文分词 ​

语法操作 ​

适用与边界 ​

字段维度分类 ​

单列索引 ​

联合索引 ​

核心机制原则 ​

最左前缀匹配原则 ​

覆盖索引 ​

索引下推 ​

常见失效场景 ​

事务 ​

基础概念 ​

事务定义 ​

引擎支持 ​

ACID 特性 ​

事务状态 ​

事务生命周期 ​

基本操作 ​

提交控制 ​

开启事务 ​

提交事务 ​

回滚事务 ​

保存点管理 ​

隐式提交 ​

事务监控 ​

隔离级别 ​

隔离背景 ​

并发异常 ​

隔离级别 ​

S09-04 MySQL-索引与事务

索引概述

索引定义

运行机制

优势与代价

适用场景

设计原则

常用语法

建表创建

表后追加

索引查看

索引删除

索引提示

数据结构

B+Tree

结构演进

核心特征

页级存储

检索链路

分裂与合并

容量计算

Hash【

物理存储分类

聚簇索引

二级索引

逻辑功能分类

主键索引

概念定义

存储机制

主键选型

索引联动

语法操作

唯一索引

概念定义

主键对比

空值特性

性能剖析

语法操作

业务实践

普通索引

概念定义

存储机制

回表机制

写缓冲支持

语法操作

优化建议

全文索引

概念定义

倒排原理

检索模式

中文分词

语法操作

适用与边界

字段维度分类

单列索引

联合索引

核心机制原则

最左前缀匹配原则

覆盖索引

索引下推

常见失效场景

事务

基础概念

事务定义

引擎支持

ACID 特性

事务状态

事务生命周期

基本操作

提交控制

开启事务

提交事务

回滚事务

保存点管理

隐式提交

事务监控

隔离级别

隔离背景

并发异常

隔离级别